package com.zhjhserver.database;

import java.sql.*;

/*************************************************************************************
 * 数据库名: zhajinhua 表名:zhjh_user
 * 用户登陆名: root  密码:TipsKevin
 * desc zhjh_user;
	+-------------+--------------+------+-----+---------+-------+
	| Field       | Type         | Null | Key | Default | Extra |
	+-------------+--------------+------+-----+---------+-------+
	| username    | varchar(20)  | YES  |     | NULL    |       |
	| passwd      | varchar(20)  | YES  |     | NULL    |       |
	| device      | varchar(100) | YES  |     | NULL    |       |
	| model       | varchar(40)  | YES  |     | NULL    |       |
	| create_time | varchar(60)  | YES  |     | NULL    |       |
	| nickname    | varchar(20)  | YES  |     | NULL    |       |
	| last_time   | varchar(20)  | YES  |     | NULL    |       |
	| sex         | varchar(20)  | YES  |     | NULL    |       |
	| icon        | varchar(40)  | YES  |     | NULL    |       |
	| score       | int(11)      | YES  |     | 0       |       |
	| grade       | int(11)      | NO   |     | 0       |       |
	| is_robot    | tinyint(4)   | NO   |     | 0       |       |
	+-------------+--------------+------+-----+---------+-------+
 *
 *******************************************************************************************/


public class MyDb
{
    // mysql驱动名称,固定的不用改变
    private static final String driver = "com.mysql.jdbc.Driver";
    // 本地数据地址,首先要安装mysql,并且创建一个叫mysql的数据库
    private static final String url = "jdbc:mysql://127.0.0.1:3306/zhajinhua";//"jdbc:mysql://127.0.0.1:3306/zhajinhua";
    // 链接数据库的,用户名，根据具体的数据库决定
    private static final String user = "root";
    // 上面用户登陆数据库的密码:
    private static final String password = "TipsKevin";
    //联接,和执行sql语句对象句柄
    private static Connection conn = null;
    protected static Statement statement = null;

    protected static final String TB_USER = "zhjh_user";
    protected static final String TB_TABLE = "zhjh_table";

    protected MyDb()
    {
    }

    public static boolean isOpen()
    {
        try {
			return (conn!=null && !conn.isClosed());
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
    }

    public static boolean open()
    {
        if (isOpen())
        {
            return true;
        }

        try {
            // 启动mysql驱动
            Class.forName(driver);

            // 联接数据库:地址，用户名，密码
            conn = DriverManager.getConnection(url, user, password);
            //如果联接不为空，并且不是关闭的，那么输出显示
            if(conn!=null && !conn.isClosed())
            {
            	System.out.println("Succeeded connecting to the Database!");
            }

            // conn创建执行sql语句的对象
            statement = conn.createStatement();

            return true;
        } catch(ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }

        return false;
    }

    public static void close()
    {
        if (conn != null)
        {
            try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

            conn = null;
            statement = null;
        }
    }

    public static void sync()
    {
    }

    public static void test()
    {
        //if (!open())
        //{
        //    return;
        //}

        try {
            // 查询数据库,要根据具体的数据库来确定
            String sql = "select * from mysql";
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("-----------------");
            System.out.println("ִ�н��������ʾ:");
            System.out.println("-----------------");
            String name = null;

            while(rs.next()) {
                // ѡ��sname�������
                name = rs.getString("username");

                // ����ʹ��ISO-8859-1�ַ�name����Ϊ�ֽ����в������洢�µ��ֽ������С�
                // Ȼ��ʹ��GB2312�ַ����ָ�����ֽ�����
                name = new String(name.getBytes("ISO-8859-1"),"GB2312");

                // ������
                System.out.println("username: " + name);
            }

            rs.close();
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }

        //close();
    }
}
